package kr.hkit.powerjava.chapter26.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.powerjava.chapter26.common.ConnectionFactory;
import kr.hkit.powerjava.chapter26.common.DbUtil;
import kr.hkit.powerjava.chapter26.dto.Department;

public class DepartmentDAO implements InterfaceDAO<Department>{
	private Connection connection;
	private PreparedStatement pstmt;

	public DepartmentDAO(Connection connection) {
		this.connection = connection;
	}

	@Override
	public void insertItem(Department item) throws SQLException {
		String sql = "insert into department values(?,?,?)"; //?에 인자를 넣어줌
		pstmt = connection.prepareStatement(sql);	
		pstmt.setInt(1, item.getDeptNo()); 	//? 첫번째 값
		pstmt.setString(2, item.getDeptName()); //? 두번째 값
		pstmt.setInt(3, item.getFloor()); //? 세번째 값
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}
	
	@Override
	public void deleteItembyNo(Department item) throws SQLException {
		String sql = "delete from department where deptno = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
//		System.out.println(pstmt);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	public void updateItembyNo(Department item) throws SQLException {
		String sql = "update department set deptName = ?, floor = ? where deptno = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getDeptName());
		pstmt.setInt(2, item.getFloor());
		pstmt.setInt(3, item.getDeptNo());
//		System.out.println(pstmt);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	public ArrayList<Department> listItems() throws SQLException {
		ArrayList<Department> list = new ArrayList<Department>();
		String sql = "select deptno, deptname, floor from department";
		pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()) { //다음레코드가 있으냐? 있으면 트루 없으면 펄스!
			list.add(new Department(
					rs.getInt("deptno"), 
					rs.getString("deptName"), 
					rs.getInt("floor")));
		}
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}

	@Override
	public Department searchItem(Department item) throws SQLException {
		Department department = null;
		String sql = "select deptno, deptname, floor from department where deptno = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNo());
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			department = new Department();
			department.setDeptNo(rs.getInt("deptNo"));
			department.setDeptName(rs.getString("deptName"));
			department.setFloor(rs.getInt("floor"));
		} 
		return department;
	}	
}